﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data.SqlServerCe;
using System.IO;
using System.Data.SqlTypes;
using System.Drawing;

namespace EffortsTracker
{
    public class Project
    {
        public int id;
        public string name;
        public DateTime initialStartDate;
        public DateTime finalEndDate;
        public int totalWorkingHours;

        public Project()
        {
        }

        public Project(string name)
        {
            this.name = name;
        }

        public Project(int id, string name)
        {
            this.id = id;
            this.name = name;
        }

        public Project(int id, string name, DateTime initialStartDate, DateTime finalEndDate, int totalWorkingHours)
        {
            this.id = id;
            this.name = name;
            this.initialStartDate = initialStartDate;
            this.finalEndDate = finalEndDate;
            this.totalWorkingHours = totalWorkingHours;
        }

        public void add()
        {
            SqlCeConnection sqlConnection = Database.getConnection();
            SqlCeCommand command = sqlConnection.CreateCommand();
            command.CommandText = "insert into projects (name) values ('" + name + "')";
            command.ExecuteNonQuery();
            command.CommandText = "SELECT @@IDENTITY";
            SqlCeDataReader sqlDataReader = command.ExecuteReader();
            if (sqlDataReader.Read())
            {
                this.id = (int)sqlDataReader.GetDecimal(0);
            }
            sqlDataReader.Close();
            sqlConnection.Close();
        }

        public void delete()
        {
            ArrayList sessions = new ArrayList();
            SqlCeConnection sqlConnection = Database.getConnection();
            SqlCeCommand command = sqlConnection.CreateCommand();
            command.CommandText = "Select id from sessions where project_id=" + this.id;
            SqlCeDataReader sqlDataReader = command.ExecuteReader();
            while (sqlDataReader.Read())
            {
                Session session = new Session(sqlDataReader.GetInt32(0));
                sessions.Add(session);
            }
            sqlDataReader.Close();
            for (int i = 0; i < sessions.Count; i++)
            {
                ((Session)sessions[i]).delete();
            }
            command.CommandText = "delete from projects where id=" + this.id;
            command.ExecuteNonQuery();
            sqlConnection.Close();
        }

        public void export(string filePath)
        {
            SqlCeConnection sqlConnection = Database.getConnection();
            SqlCeCommand command = sqlConnection.CreateCommand();

            StreamWriter keyStrokesWriter = new StreamWriter(filePath + "\\key_events.csv");
            command.CommandText = "Select date from keyboard_events, sessions, projects where keyboard_events.session_id=sessions.id and sessions.project_id=projects.id and projects.id=" + this.id + " order by date";
            SqlCeDataReader sqlDataReader = command.ExecuteReader();
            while (sqlDataReader.Read())
            {
                keyStrokesWriter.WriteLine(sqlDataReader.GetDateTime(0).ToString());
            }
            keyStrokesWriter.Close();

            StreamWriter mouseClicksWriter = new StreamWriter(filePath + "\\mouse_clicks.csv");
            command.CommandText = "Select date from mouse_clicks, sessions, projects where mouse_clicks.session_id=sessions.id and sessions.project_id=projects.id and projects.id=" + this.id + " order by date";
            sqlDataReader = command.ExecuteReader();
            while (sqlDataReader.Read())
            {
                mouseClicksWriter.WriteLine(sqlDataReader.GetDateTime(0).ToString());
            }
            mouseClicksWriter.Close();

            command.CommandText = "Select screen_shot, date from screen_shots, sessions, projects where screen_shots.session_id=sessions.id and sessions.project_id=projects.id and projects.id=" + this.id + " order by date";
            sqlDataReader = command.ExecuteReader();
            while (sqlDataReader.Read())
            {
                DateTime date = sqlDataReader.GetDateTime(1);
                SqlBinary sqlBinary = sqlDataReader.GetSqlBinary(0);
                Image image = ScreenShot.setImageFromByteArray(sqlBinary.Value);
                image.Save(filePath + "\\image_" + date.Year + formatNumber(date.Month) + formatNumber(date.Day) + formatNumber(date.Hour) + formatNumber(date.Minute) + formatNumber(date.Second) + ".jpg");
            }

            sqlDataReader.Close();
            sqlConnection.Close();
        }

        private string formatNumber(int number)
        {
            if (number < 10 && number >= 0)
                return "0" + number;
            return number + "";
        }

        public string getFormattedHours()
        {
            int totalMinutes = (int)((double)totalWorkingHours / 60.0);
            int totalHours = (int)((double)totalMinutes / 60.0);
            string result = "";
            if (totalHours < 10)
                result += "0";
            result += totalHours + ":";
            int finalMinutes = (totalMinutes - totalHours * 60);
            if (finalMinutes < 10)
                result += "0";
            result += finalMinutes;
            return result;
        }

        public static Project getProject(int id)
        {
            Project project = null;
            SqlCeConnection sqlConnection = Database.getConnection();
            SqlCeCommand command = sqlConnection.CreateCommand();
            command.CommandText = "Select name from projects where id=" + id;
            SqlCeDataReader sqlDataReader = command.ExecuteReader();
            if (sqlDataReader.Read())
            {
                project = new Project(id // ID
                    , sqlDataReader.GetString(0)); // Name
            }
            sqlDataReader.Close();
            sqlConnection.Close();
            return project;
        }

        public static Project[] getAllProjects()
        {
            ArrayList projects = new ArrayList();
            SqlCeConnection sqlConnection = Database.getConnection();
            SqlCeCommand command = sqlConnection.CreateCommand();
            command.CommandText = "Select id, name from projects";
            SqlCeDataReader sqlDataReader = command.ExecuteReader();
            while (sqlDataReader.Read())
            {
                Project project = new Project(sqlDataReader.GetInt32(0)// ID
                    , sqlDataReader.GetString(1)); // Name
                projects.Add(project);
            }
            sqlDataReader.Close();
            sqlConnection.Close();
            return (Project[])projects.ToArray(typeof(Project));
        }

        public static Project[] getAllFullProjects()
        {
            ArrayList projects = new ArrayList();
            SqlCeConnection sqlConnection = Database.getConnection();
            SqlCeCommand command = sqlConnection.CreateCommand();
            command.CommandText = "SELECT projects.id, projects.name, MIN(sessions.initial_start_date) AS Expr1, MAX(sessions.final_end_date) AS Expr2, SUM(DATEDIFF(ss, sessions.initial_start_date, sessions.final_end_date)) AS Expr3 FROM projects INNER JOIN sessions ON projects.id = sessions.project_id GROUP BY projects.id, projects.name";//Select projects.id, projects.name, min(initial_start_date), max(final_end_date), sum(datediff(ss,initial_start_date,final_end_date)) from projects, sessions where projects.id=sessions.project_id group by GROUP BY projects.id, projects.name";
            SqlCeDataReader sqlDataReader = command.ExecuteReader();
            while (sqlDataReader.Read())
            {
                Project project = new Project(sqlDataReader.GetInt32(0)// ID
                    , sqlDataReader.GetString(1)
                    , sqlDataReader.GetDateTime(2)
                    , sqlDataReader.GetDateTime(3)
                    , sqlDataReader.GetInt32(4)); // Name
                projects.Add(project);
            }
            sqlDataReader.Close();
            sqlConnection.Close();
            return (Project[])projects.ToArray(typeof(Project));
        }
    }
}